package com.tianlang.dao.impl;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.orm.hibernate4.support.HibernateDaoSupport;
import org.springframework.stereotype.Service;

import com.tianlang.dao.IBaseDao;
import com.tianlang.util.CompareColumn;
import com.tianlang.util.Constants;
import com.tianlang.util.SortColumn;

@SuppressWarnings({ "unchecked", "rawtypes", "finally", "unused" })
@Service("baseDao")
public class BaseDao<T> extends HibernateDaoSupport implements IBaseDao<T> {
	Logger log = Logger.getLogger(BaseDao.class);

	@Resource(name = "sessionFactory")
	public void setSF(SessionFactory sessionFactory) {
		super.setSessionFactory(sessionFactory);
	}

	/***************************** 增 ******************************/
	@Override
	public void save(Object entity) {
		getHibernateTemplate().save(entity);
		getHibernateTemplate().flush();
	}

	@Override
	public void saveOrUpdate(Object entiry) {
		getHibernateTemplate().saveOrUpdate(entiry);
		getHibernateTemplate().flush();
	}

	@Override
	public void saveMerge(Object entity) {
		getHibernateTemplate().merge(entity);
		getHibernateTemplate().flush();
	}

	@Override
	public void saveOrUpdateAll(Collection c) {
		getHibernateTemplate().saveOrUpdate(c);
		// getHibernateTemplate().saveOrUpdateAll(c);
	}

	/***************************** 删 ******************************/
	@Override
	public void delete(Object entity) {
		getHibernateTemplate().delete(entity);
	}

	@Override
	public void delete(Class clazz, Serializable id) {
		Object o = getObject(clazz, id);
		if (o != null)
			getHibernateTemplate().delete(getObject(clazz, id));
	}

	@Override
	public void delete(final String hql) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createQuery(hql).executeUpdate();
			}

		});
	}

	@Override
	public void delete(final String query, final Object[] objects, final Type[] types) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createQuery(query).setParameters(objects, types).executeUpdate();
			}
		});
	}

	/***************************** 改 ******************************/
	@Override
	public void update(Object entity) {
		getHibernateTemplate().update(entity);
		getHibernateTemplate().flush();
	}

	/***************************** 查 ******************************/
	@Override
	public Object getObject(Class entity, Serializable id) {
		return getHibernateTemplate().get(entity, id);
	}

	@Override
	public Object load(Class entity, Serializable id) {
		return getHibernateTemplate().load(entity, id);
	}

	@Override
	public List find(String query) {
		return getHibernateTemplate().find(query);
	}

	@Override
	public List find(String query, Object parameter) {
		return getHibernateTemplate().find(query, parameter);
	}

	@Override
	public List find(String query, Object[] objects) {
		return getHibernateTemplate().find(query, objects);
	}

	@Override
	public List getObjects(Class clazz) {
		return getHibernateTemplate().loadAll(clazz);
	}

	@Override
	public List findAll(Class entity) {
		return getHibernateTemplate().find("from " + entity.getName());
	}

	@Override
	public List find(final String query, final Object[] objects, final Type[] types) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createQuery(query).setParameters(objects, types).list();
			}
		});
	}

	@Override
	public List findByNamedQuery(String namedQuery) {
		return getHibernateTemplate().findByNamedQuery(namedQuery);
	}

	@Override
	public List findByNamedQuery(String query, Object parameter) {
		return getHibernateTemplate().findByNamedQuery(query, parameter);
	}

	@Override
	public List findByNamedQuery(String query, Object[] parameters) {
		return getHibernateTemplate().findByNamedQuery(query, parameters);
	}

	@Override
	public String getConfigurationValue(final String key) {
		return (String) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				String hql = "select scd.paramvalue from Sysparam scd where scd.paramkey =:key";
				Query query = session.createQuery(hql);
				query.setString("key", key);
				List<String> valueList = query.list();
				return valueList.size() == 0 ? null : valueList.get(0);
			}
		});

	}

	@Override
	public Integer getTotalSize(final Class clz, final Map<String, Object> searchMap, final String groupColumn) {
		return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = session.createCriteria(clz);
				criteria.setProjection(Projections.groupProperty(groupColumn));
				if (searchMap != null) {
					Iterator iter = searchMap.entrySet().iterator();
					List<String> fieldColumns = new ArrayList<String>();
					List searchValues = new ArrayList();
					while (iter.hasNext()) {
						Map.Entry<String, Object> entry = (Map.Entry<String, Object>) iter.next();
						String key = entry.getKey();
						Object val = entry.getValue();
						fieldColumns.add(key);
						searchValues.add(val);
					}
					if (fieldColumns != null && fieldColumns.size() != 0) {
						for (int k = 0; k < fieldColumns.size(); k++) {
							criteria.add(Restrictions.eq((String) fieldColumns.get(k), searchValues.get(k)));
						}
					}
				}
				return getCount(criteria);
			}
		});
	}

	protected Integer getCount(Criteria criteria) {
		criteria.setProjection(Projections.rowCount());
		List list = criteria.list();
		if (list != null && list.size() > 0) {
			return Integer.valueOf(list.get(0).toString());
		} else {
			return 0;
		}
	}

	public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	@Override
	public List getObjectByCreator(final Class clz, final Long creatorId, final String updateTime) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = session.createCriteria(clz);
				criteria.add(Restrictions.eq("creator.id", creatorId));
				try {
					criteria.add(Restrictions.ge("updateDatetime", sdf.parse(updateTime)));
				} catch (ParseException e) {
					e.printStackTrace();
				}
				return criteria.list();
			}
		});
	}

	@Override
	public List getObjectByCreator(final Class clz, final Long creatorId) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = session.createCriteria(clz);
				criteria.add(Restrictions.eq("creator.id", creatorId));
				return criteria.list();
			}
		});
	}

	@Override
	public Object getObjectByParams(final Class clz, final Map<String, Object> searchMap) {
		return getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				List<String> params = new ArrayList<String>();
				List<Object> values = new ArrayList<Object>();
				String hql = "from " + clz.getSimpleName() + " where ";

				if (searchMap != null) {
					Iterator iter = searchMap.entrySet().iterator();
					while (iter.hasNext()) {
						Map.Entry<String, Object> entry = (Map.Entry<String, Object>) iter.next();
						String key = entry.getKey();
						Object val = entry.getValue();
						params.add(key);
						values.add(val);
					}
					for (int i = 0; i < params.size() - 1; i++) {
						hql = hql + params.get(i) + "=? and ";
					}
					hql = hql + params.get(params.size() - 1) + "=?";
				}

				Query query = session.createQuery(hql);
				for (int i = 0; i < values.size(); i++) {
					Object obj = values.get(i);
					setParameterValue(query, i, obj);
				}
				query.setFirstResult(0);
				query.setMaxResults(1);
				List list = query.list();
				if (list == null || list.isEmpty()) {
					return null;
				} else {
					return list.get(0);
				}
			}
		});
	}

	/**
	 * 预编译参数赋值
	 * 
	 * @param query
	 * @param key
	 * @param value
	 */
	private void setParameterValue(Query query, int key, Object value) {
		if (null == value) {
			return;
		} else if (value instanceof Boolean) {
			query.setBoolean(key, ((Boolean) value).booleanValue());
		} else if (value instanceof String) {
			query.setString(key, (String) value);
		} else if (value instanceof Integer) {
			query.setInteger(key, ((Integer) value).intValue());
		} else if (value instanceof Long) {
			query.setLong(key, ((Long) value).longValue());
		} else if (value instanceof Float) {
			query.setFloat(key, ((Float) value).floatValue());
		} else if (value instanceof Double) {
			query.setDouble(key, ((Double) value).doubleValue());
		} else if (value instanceof BigDecimal) {
			query.setBigDecimal(key, (BigDecimal) value);
		} else if (value instanceof Byte) {
			query.setByte(key, ((Byte) value).byteValue());
		} else if (value instanceof Calendar) {
			query.setCalendar(key, (Calendar) value);
		} else if (value instanceof Character) {
			query.setCharacter(key, ((Character) value).charValue());
		} else if (value instanceof Timestamp) {
			query.setTimestamp(key, (Timestamp) value);
		} else if (value instanceof Date) {
			query.setDate(key, (Date) value);
		} else if (value instanceof Short) {
			query.setShort(key, ((Short) value).shortValue());
		}
	}

	@Override
	public <E> List<E> getListByWhere(final String where, final Object[] objects, final Integer pageIndex,
			final Integer pageSize) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(where);
				if (objects != null) {
					int objectsize = objects.length;
					if (objectsize > 0) {
						for (int i = 0; i < objectsize; i++) {
							query.setParameter(i, objects[i]);
						}
					}
				}
				if (pageIndex != null) {
					query.setFirstResult((pageIndex - 1) * pageSize);
				}
				if (pageSize != null) {
					query.setMaxResults(pageSize);
				}
				query.setCacheable(true);//
				List<E> _list = query.list();
				return _list;

			}
		});
	}

	@Override
	public List<Object> findByExample(Object obj, Integer pageIndex, Integer pageSize) {
		Integer firstResult = 0;
		if (pageIndex != null && pageSize != null) {
			firstResult = (pageIndex - 1) * pageSize;
			return getHibernateTemplate().findByExample(obj, firstResult, pageSize);
		}
		return null;
	}

	@Override
	public Object getCount(final String where, final Object[] values) {
		return (Object) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(where);
				if (values != null) {
					int objectsize = values.length;
					if (objectsize > 0) {
						for (int i = 0; i < objectsize; i++) {
							query.setParameter(i, values[i]);
						}
					}
				}

				if (query == null) {
					return null;
				}
				return query.uniqueResult();

			}
		});
	}

	@Override
	public Integer getTotalRecordCount(String where, Object[] objects) {
		Integer counts = 0;
		Long _l = (Long) this.getCount(where, objects);
		if (_l != null) {
			counts = _l.intValue();
		}
		return counts;

	}

	@Override
	public <E> List<E> getObjectsWhere(final String where, final Object[] objects, final Integer start,
			final Integer size) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(where);
				if (objects != null) {
					int objectsize = objects.length;
					if (objectsize > 0) {
						for (int i = 0; i < objectsize; i++) {
							query.setParameter(i, objects[i]);
						}
					}
				}
				query.setFirstResult(start);
				query.setMaxResults(size);
				query.setCacheable(true);//
				List<E> _list = query.list();
				return _list;
			}
		});
	}

	@Override
	public Integer getTotalRecordCount(final String where, final boolean cache) {
		return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				try {
					Query query = session.createQuery(where);
					query.setCacheable(cache);
					Long count = (Long) query.uniqueResult();
					if (count == null) {
						return 0;
					}
					Integer counts = count.intValue();
					return counts;
				} catch (Exception e) {
					log.debug("查询对象出错 getTotalRecordCount: " + where + " " + e.getMessage());
				} finally {
					return 0;
				}

			}
		});
	}

	// /////////////////////////////////表的级联//////////////////////////////////////
	@Override
	public Integer getPaginationObjects(final Class clz, final Map<String, Object> searchMap,
			final Map<String, Object> searchClassMap, final List<CompareColumn> compareColumns) {
		return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = session.createCriteria(clz);
				processSearchCondiction(searchMap, criteria);
				compareProcess(compareColumns, criteria);
				processSearchClassCondiction(searchClassMap, criteria);
				return getCount(criteria);
			}
		});
	}

	@Override
	public List<Object> getPaginationObjects(final Class clz, final int pageIndex, final int pageSize,
			final Map<String, Object> searchMap, final Map<String, Object> searchClassMap,
			final List<CompareColumn> compareColumns, final List<SortColumn> scs) {
		return (List<Object>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = session.createCriteria(clz);
				processSearchCondiction(searchMap, criteria);
				processSearchClassCondiction(searchClassMap, criteria);
				compareProcess(compareColumns, criteria);
				processSortCondiction(scs, criteria);
				if (pageSize > 0) {
					criteria.setFirstResult((pageIndex - 1) * pageSize);
					criteria.setMaxResults(pageSize);
				}
				List<Object> _list = criteria.list();
				session.close();
				return _list;
			}
		});

	}

	@Override
	public List<Object> getPaginationObjects(final Class clz, final int pageIndex, final int pageSize,
			final Map<String, Object> searchMap, final Map<String, Object> searchClassMap, final List<SortColumn> scs) {
		return (List<Object>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = session.createCriteria(clz);
				processSearchCondiction(searchMap, criteria);
				processSearchClassCondiction(searchClassMap, criteria);
				processSortCondiction(scs, criteria);
				criteria.setFirstResult((pageIndex - 1) * pageSize);
				criteria.setMaxResults(pageSize);
				List li = criteria.list();
				return li;
			}
		});
	}

	@Override
	public List<Object> getPaginationObjects(final Class clz, final int pageIndex, final int pageSize,
			final Map<String, Object> searchMap, final Map<String, Object> searchClassMap, final List<SortColumn> scs,
			final String groupColumn) {
		return (List<Object>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = session.createCriteria(clz);
				criteria.setProjection(Projections.projectionList().add(Projections.groupProperty(groupColumn))
						.add(Projections.rowCount()));
				processSearchCondiction(searchMap, criteria);
				processSearchClassCondiction(searchClassMap, criteria);
				processSortCondiction(scs, criteria);
				criteria.setFirstResult((pageIndex - 1) * pageSize);
				criteria.setMaxResults(pageSize);
				List li = criteria.list();
				List list = criteria.list();
				Iterator it = list.iterator();
				return criteria.list();
			}
		});
	}

	@Override
	public Integer getPaginationObjects(final Class clz, final Map<String, Object> searchMap,
			final Map<String, Object> searchClassMap) {
		return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = session.createCriteria(clz);
				processSearchCondiction(searchMap, criteria);
				processSearchClassCondiction(searchClassMap, criteria);
				return getCount(criteria);
			}
		});
	}

	@Override
	public List getObjectsByParams(final Class clz, final Map<String, Object> searchMap,
			final Map<String, Object> searchClassMap, final List<SortColumn> scs) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Criteria criteria = session.createCriteria(clz);
				List<String> fieldColumns = new ArrayList<String>();
				List searchValues = new ArrayList();
				if (searchMap != null) {
					Iterator iter = searchMap.entrySet().iterator();
					while (iter.hasNext()) {
						Map.Entry<String, Object> entry = (Map.Entry<String, Object>) iter.next();
						String key = entry.getKey();
						Object val = entry.getValue();
						fieldColumns.add(key);
						searchValues.add(val);
					}
				}
				processSearchClassCondiction(searchClassMap, criteria);
				if (fieldColumns != null && fieldColumns.size() != 0) {
					for (int k = 0; k < fieldColumns.size(); k++) {
						if (searchValues.get(k) == null) {
							criteria.add(Restrictions.isNull((String) fieldColumns.get(k)));
						} else if (searchValues.get(k).getClass().getName().equals("java.lang.String")) {
							String values = (String) searchValues.get(k);
							if (values.startsWith(Constants.Like_Start)) {
								criteria.add(Restrictions.like((String) fieldColumns.get(k),
										values.replace(Constants.Like_Start, ""), MatchMode.START));
							} else if (values.startsWith(Constants.Like)) {
								criteria.add(Restrictions.like((String) fieldColumns.get(k),
										values.replace(Constants.Like, ""), MatchMode.ANYWHERE));
							} else {
								criteria.add(Restrictions.eq((String) fieldColumns.get(k), searchValues.get(k)));
							}
						} else if (searchValues.get(k).getClass().getName().equals("[Ljava.lang.String;"))
							criteria.add(Restrictions.in((String) fieldColumns.get(k), (String[]) searchValues.get(k)));
						else if (searchValues.get(k).getClass().getName().equals("[Ljava.lang.Long;"))
							criteria.add(Restrictions.in((String) fieldColumns.get(k), (Long[]) searchValues.get(k)));
						else
							criteria.add(Restrictions.eq((String) fieldColumns.get(k), searchValues.get(k)));

					}
				}
				processSortCondiction(scs, criteria);
				return criteria.list();
			}
		});

	}

	private void processSearchCondiction(Map<String, Object> searchMap, Criteria criteria) {
		if (searchMap == null) {
			return;
		}
		Iterator iter = searchMap.entrySet().iterator();
		while (iter.hasNext()) {
			Map.Entry<String, Object> entry = (Map.Entry<String, Object>) iter.next();
			String key = entry.getKey();
			Object val = entry.getValue();
			if (val.getClass().getName().equals("java.lang.String")) {

				String values = (String) val;
				if (values.startsWith(Constants.Like_Start)) {
					criteria.add(Restrictions.like(key, values.replace(Constants.Like_Start, ""), MatchMode.START));
				} else if (values.startsWith(Constants.Like)) {
					criteria.add(Restrictions.like(key, values.replace(Constants.Like, ""), MatchMode.ANYWHERE));
				} else {
					criteria.add(Restrictions.eq(key, val));
				}

			} else if (val.getClass().getName().equals("[Ljava.lang.String;"))
				criteria.add(Restrictions.in(key, (String[]) val));
			else
				criteria.add(Restrictions.eq(key, val));
		}
	}

	private void compareProcess(List<CompareColumn> compareColumns, Criteria criteria) {
		if (compareColumns != null) {
			for (int k = 0; k < compareColumns.size(); k++) {
				Object value = compareColumns.get(k).getValue();
				String key = compareColumns.get(k).getKey();
				String dir = compareColumns.get(k).getDir();
				if (value instanceof Date || value instanceof Integer || value instanceof Long
						|| value instanceof Boolean || value instanceof Character || value instanceof Byte
						|| value instanceof Float || value instanceof Double) {
					if (dir.trim().equalsIgnoreCase("ge"))
						criteria.add(Restrictions.ge(key, value));
					else if (dir.trim().equalsIgnoreCase("le"))
						criteria.add(Restrictions.le(key, value));
					else if (dir.trim().equalsIgnoreCase("gt")) {
						criteria.add(Restrictions.gt(key, value));
					} else if (dir.trim().equalsIgnoreCase("lt")) {
						criteria.add(Restrictions.lt(key, value));
					} else if (dir.trim().equalsIgnoreCase("eq")) {
						criteria.add(Restrictions.eq(key, value));
					} else if (dir.trim().equalsIgnoreCase("ne")) {
						criteria.add(Restrictions.ne(key, value));
					}
				} else {
					criteria.add(Restrictions.le(key, value));
				}

			}
		}
	}

	private void processSearchClassCondiction(Map<String, Object> searchClassMap, Criteria criteria) {
		if (searchClassMap == null) {
			return;
		}
		Iterator classIter = searchClassMap.entrySet().iterator();
		List<String> fieldClassColumns = new ArrayList<String>();
		List fieldClassValues = new ArrayList();
		while (classIter.hasNext()) {
			Map.Entry<String, Object> entry = (Map.Entry<String, Object>) classIter.next();
			String key = entry.getKey();
			Object val = entry.getValue();
			fieldClassColumns.add(key);
			fieldClassValues.add(val);
		}
		List<String> objs = new ArrayList<String>();
		if (fieldClassColumns != null && fieldClassColumns.size() > 0) {
			for (int k = 0; k < fieldClassColumns.size(); k++) {
				if (String.valueOf(fieldClassColumns.get(k)).contains(".")) {
					String[] strs = (String.valueOf(fieldClassColumns.get(k))).split("\\.");
					if (!objs.contains(strs[0])) {
						criteria.createAlias(strs[0], "object_" + strs[0]);
						objs.add(strs[0]);
					}
					if (fieldClassValues.get(k).toString().startsWith(Constants.Like_Start)) {
						criteria.add(Restrictions.like("object_" + strs[0] + "." + strs[1],
								fieldClassValues.get(k).toString().replace(Constants.Like_Start, ""), MatchMode.START));
					} else if (fieldClassValues.get(k).toString().startsWith(Constants.Like)) {
						criteria.add(Restrictions.like("object_" + strs[0] + "." + strs[1],
								fieldClassValues.get(k).toString().replace(Constants.Like, ""), MatchMode.ANYWHERE));
					} else {
						criteria.add(Restrictions.eq("object_" + strs[0] + "." + strs[1], fieldClassValues.get(k)));
					}

				}
			}
		}
	}

	private void processSortCondiction(List<SortColumn> scs, Criteria criteria) {
		if (scs == null || scs.size() == 0) {
			return;
		}
		for (SortColumn sc : scs) {
			if (!sc.getSortKeys().startsWith("id.")) {
				if (sc.getSortDir().equalsIgnoreCase("ASC")) {
					if (sc.getSortKeys().indexOf(".") != -1) {
						String aliasKey = sc.getSortKeys().substring(0, sc.getSortKeys().indexOf("."));
						criteria.createAlias(aliasKey, aliasKey);
						criteria.addOrder(Order.asc(sc.getSortKeys()));
					} else {
						criteria.addOrder(Order.asc(sc.getSortKeys()));
					}
				} else {

					criteria.addOrder(Order.desc(sc.getSortKeys()));
				}

			} else {
				if (!sc.getSortKeys().startsWith("id.")) {
					if (sc.getSortKeys().indexOf(".") != -1) {
						String aliasKey = sc.getSortKeys().substring(0, sc.getSortKeys().indexOf("."));
						criteria.createAlias(aliasKey, aliasKey);
						criteria.addOrder(Order.desc(sc.getSortKeys()));
					} else
						criteria.addOrder(Order.desc(sc.getSortKeys()));
				} else {

					criteria.addOrder(Order.desc(sc.getSortKeys()));
				}
			}
		}

	}

	// /////////////////////////////////sql////////////////////////////////////////
	@Override
	public List<Object> querySQL(String sql, Object[] object, Integer pageIndexs, Integer pageSizes,
			Class<?> pojoClass) {
		final String hql1 = sql;
		final Object[] objects = object;
		final Integer pageIndex = pageIndexs;
		final Integer pageSize = pageSizes;
		final Class<?> cname = pojoClass;
		return (List<Object>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createSQLQuery(hql1).addEntity(cname);
				query.setCacheable(true);
				if (objects != null) {
					int objectsize = objects.length;
					if (objectsize > 0) {
						for (int i = 0; i < objectsize; i++) {
							query.setParameter(i, objects[i]);
						}
					}
				}
				if (pageIndex != 0 && pageSize != 0) {
					query.setFirstResult((pageIndex - 1) * pageSize);
					query.setMaxResults(pageSize);
				}
				return query.list();
			}
		});
	}

	@Override
	public List<Object> querySQLDto(String sql, Object[] object, Integer pageIndexs, Integer pageSizes,
			Class<?> pojoClass) {
		final String hql1 = sql;
		final Object[] objects = object;
		final Integer pageIndex = pageIndexs;
		final Integer pageSize = pageSizes;
		final Class<?> cname = pojoClass;
		return (List<Object>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createSQLQuery(hql1).setResultTransformer(Transformers.aliasToBean(cname));
				// Query query = session.createSQLQuery(hql1);
				// Query query =
				// session.createSQLQuery(hql1).setResultTransformer(Transformers.aliasToBean(cname));
				if (objects != null) {
					int objectsize = objects.length;
					if (objectsize > 0) {
						for (int i = 0; i < objectsize; i++) {
							query.setParameter(i, objects[i]);
						}
					}
				}
				if (pageIndex != 0 && pageSize != 0) {
					query.setFirstResult((pageIndex - 1) * pageSize);
					query.setMaxResults(pageSize);
				}

				return query.list();
			}
		});
	}

	@Override
	public Integer querySQLCount(String sqlb) {
		final String sql = sqlb;
		return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
			public Integer doInHibernate(Session session) throws HibernateException {
				Query query = session.createSQLQuery(sql);
				// Query query = session.createSQLQuery(hql1).addEntity(cname);
				// Query query =
				// session.createSQLQuery(hql1).setResultTransformer(Transformers.aliasToBean(cname));
				Integer count = (Integer) query.uniqueResult();
				if (count == null) {
					return 0;
				}
				return count;
			}
		});
	}

	@Override
	public List<Object> querySQL(String sql, Object[] object, Class<?> pojoClass) {
		final String hql1 = sql;
		final Object[] objects = object;
		final Class<?> cname = pojoClass;
		return (List<Object>) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createSQLQuery(hql1);
				// Query query = session.createSQLQuery(hql1).addEntity(cname);
				// Query query =
				// session.createSQLQuery(hql1).setResultTransformer(Transformers.aliasToBean(cname));
				if (objects != null) {
					int objectsize = objects.length;
					if (objectsize > 0) {
						for (int i = 0; i < objectsize; i++) {
							query.setParameter(i, objects[i]);
						}
					}
				}
				return query.list();
			}
		});
	}

	@Override
	public void executeSQL(final String Sql) {
		getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				// Query query = session.createSQLQuery(hql1).addEntity(cname);
				// Query query =
				// session.createSQLQuery(hql1).setResultTransformer(Transformers.aliasToBean(cname));
				return session.createSQLQuery(Sql).executeUpdate();
			}

		});
	}

}
